package ru.michaeltyan.familyconnector.dao.impl;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import ru.michaeltyan.familyconnector.beans.Foodstuff;
import ru.michaeltyan.familyconnector.beans.FoodstuffList;
import ru.michaeltyan.familyconnector.dao.FoodstuffManagerDao;

public class FoodstuffManagerDaoImpl extends HibernateDaoSupport implements FoodstuffManagerDao {

	protected final Log logger = LogFactory.getLog(getClass());

	public List<Foodstuff> getAllFoodstuffs() {
		@SuppressWarnings("unchecked")
		List<Foodstuff> foodstuffs = getHibernateTemplate().find("from Foodstuff");
		return foodstuffs;
	}

	public List<FoodstuffList> getAllFoodstuffLists() {
		@SuppressWarnings("unchecked")
		List<FoodstuffList> foodstuffLists = getHibernateTemplate().find("from FoodstuffList");
		return foodstuffLists;
	}

	public Foodstuff getFoodstuffById(Long id, Connection conn) throws SQLException {
		Foodstuff foodstuff = new Foodstuff();
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery("SELECT * FROM foodstuff WHERE id=" + id);
		if (rs.next()) {
			foodstuff.setId(id);
			foodstuff.setFoodstuffName(rs.getString(2));
			foodstuff.setPrice(rs.getFloat(3));
			foodstuff.setQuantity(rs.getFloat(4));
		}
		return foodstuff;
	}

	public List<Long> getAllIdsOfFoodstuffLists(Connection conn, Long foodstuffListId) throws SQLException {
		List<Long> ids_inCache = new ArrayList<Long>();
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery("SELECT id_foodstuff FROM foodstufflist_foodstuff WHERE id_foodstuffList=" + foodstuffListId);
		while (rs.next()) {
			ids_inCache.add(rs.getLong(1));
		}
		return ids_inCache;
	}

	public Connection openConnection() throws ClassNotFoundException, SQLException, FileNotFoundException, IOException {
		Connection conn = null;
		Properties prop = new Properties();
		String path = System.getenv("CATALINA_HOME") + "\\webapps\\familyconnector\\WEB-INF\\classes\\properties\\database.properties";
		prop.load(new FileInputStream(path));
		Class.forName(prop.getProperty("jdbc.driverClassName"));
		String url = prop.getProperty("jdbc.url");
		conn = DriverManager.getConnection(url, prop.getProperty("jdbc.username"), prop.getProperty("jdbc.password"));
		return conn;
	}

	public void closeConnection(Connection conn) throws SQLException {
		conn.close();
	}

}
